#include "playquery.h"
#include "DB_Manager.h"

PlayQuery::PlayQuery( QWidget* parent )
	: QWidget( parent )
{
	setupUi( this );
	orderpaint = Qt::AscendingOrder;
	
  setWindowFlags ( Qt::Window );
	
  setWindowModality ( Qt::NonModal );

	setWindowTitle(tr("Gestione Banca dati mysql"));
}

void PlayQuery::init( DB_Manager * dbin )
{
	 line = dbin;
	 if (line->isValid()) {
		 db = line->current();
	 } else {
		 QMessageBox::warning(0, tr("Error SQL conn.!"),tr("Unable to connect on db"));
		 label->setText(tr("Unable to connect on db"));
		 return;
	 }
	 tableedit->setDB(db);
	 label->setText(line->info());
	  line->FillComboFixQuery(cachequery);
	 ReloadTables();
	 stackedWidget->setCurrentIndex(0);
	 codecs->clear();
	 codecs->addItem("Default from system 0",0);
     QStringList enc;
	foreach (QByteArray s, QTextCodec::availableCodecs())   {
		QTextCodec *trase = QTextCodec::codecForName(s);
		int nur = trase->mibEnum ();
		codecs->addItem(QString("%1/%2").arg(s.data()).arg(nur),nur);
	}
	 /*
	 SELECT * FROM %1 PROCEDURE ANALYSE() 
	 */
	 QStringList userquery = setter.value("query/user").toStringList();
	 if (userquery.size() > 0) {
	 FilluserQuery();
	 }
	 /////QSettings setter;
	 
	 connect(edittablelist, SIGNAL(currentIndexChanged(QString)),this, SLOT(EditTable(QString) ));
     connect(tablestructure, SIGNAL(currentIndexChanged(QString)),this, SLOT(StructTable(QString) ));
	 connect(edittablelist, SIGNAL(currentIndexChanged(QString)),this, SLOT(updateMenu() ));
	 connect(cachequery, SIGNAL(currentIndexChanged(int)),this, SLOT(show_paste_query(int) ));
	 connect(showtable, SIGNAL(currentIndexChanged(QString)),this, SLOT(show_table(QString) ));
	 connect(pushButton_5, SIGNAL(clicked()), this, SLOT(exec_query_user()));
	 connect(pushButton_2, SIGNAL(clicked()), this, SLOT(export_show()));
	 connect(pushButton, SIGNAL(clicked()), this, SLOT(print_show()));
	 connect(pushButton_3, SIGNAL(clicked()), this, SLOT(save_user_query()));
	 connect(pushButton_4, SIGNAL(clicked()), textquery, SLOT(clear()));
	 connect(toolButton, SIGNAL(clicked()), this, SLOT(clear_user_query()));
	 connect(pushButton_7, SIGNAL(clicked()), this, SLOT(user_export_mysql()));
	 connect(pushButton_8, SIGNAL(clicked()), this, SLOT(OpenFileQuery()));
     connect(pushButton_9, SIGNAL(clicked()), this, SLOT(ExecQueryList()));
	  /////////////QStringList list;
       /////////list << tr("Show Table") << tr("Edit Table") << tr("Structure Table") << tr("Import mysql query file"); 
	 //////////////QStringListModel *menus = new QStringListModel(list,this); 
      ////////////menu->setModel(menus);
	  stackedWidget->setCurrentIndex(1);
}

void PlayQuery::EditTable( const QString t)
{
	QStringList hottables = line->Hotlist();
	if (hottables.contains(t)) {
		QMessageBox::warning(0, tr("Error SQL!"),tr("HotTable not allow to edit! Only show"));
	return;
	}
   tableedit->PaintTable(t);
	
}
void PlayQuery::ReloadTables()
{
	  
	 line->FillCombo(showtable);
	 line->FillCombo(edittablelist);
	 line->FillCombo(tablestructure);
	 
}

void PlayQuery::StructTable( QString t ) 
{
         QString creas =  line->TableMysqlStruct(t,db,true,true);
          textEdit->setPlainText(creas);

}

void PlayQuery::updateMenu()
{
      QMenu *editmenu = tableedit->CreateMenu();
	 pushButton_6->setMenu(editmenu);
} 

void PlayQuery::user_export_mysql()
{
	QString table = showtable->currentText();
	QString cfileName = QFileDialog::getSaveFileName(0, "Export MYSQL table "+table,table+".sql", "*.sql");
    if (cfileName.size() > 0  ) {
                 if (!cfileName.endsWith(".sql")) {
                     cfileName = cfileName+".sql"; 
                 }
    }  else {
    return;
    }

        //////int default_mib = 106;
        int usermib = codecs->itemData(codecs->currentIndex()).toInt();
        
        QString stream;
        stream.append(line->TableMysqlStruct(table,db,true));
        stream.append("\n");
        if (usermib !=0) {
        stream.append(line->StreamTableDump(table,db,usermib));
        } else {
        stream.append(line->StreamTableDump(table,db));
        }
        
        QTextCodec *cutf8 = QTextCodec::codecForMib(106);
        
        QTextCodec *codecx = QTextCodec::codecForMib(usermib);
        QFile f( cfileName );
        if ( f.open( QFile::WriteOnly | QFile::Text ) )
        {
        QTextStream sw( &f );
        if (usermib !=0) {
        sw.setCodec(codecx);
        } else {
        sw.setCodec(cutf8);
        }
        sw << stream;
        f.close();
        }
    
}

void PlayQuery::save_user_query()
{
	bool ok;
	QStringList userquery = setter.value("query/user").toStringList();
	
	if (!model_show->lastError().isValid()) {
		QString queryuser = encodeBase64(textquery->toPlainText().trimmed());
         QString uname = QInputDialog::getText(this, tr("Query name to display.."),tr("Name:"), QLineEdit::Normal,"Query_Name", &ok);
    
		if (ok && uname.size() > 0) {
		QString line = QString("%1|%2").arg(encodeBase64(uname)).arg(queryuser);
		userquery.append(line);
		setter.setValue("query/user",userquery);
		FilluserQuery();
		}
		
	}
	
}

void PlayQuery::clear_user_query()
{
	setter.setValue("query/user",QStringList());
	FilluserQuery();
	
}




void PlayQuery::FilluserQuery()
{
	UserQuery->clear();
	QStringList userquery = setter.value("query/user").toStringList();
	  for (int i = 0; i < userquery.size(); ++i)   {
	  	QString qx = userquery.at(i);
	  	QStringList nare = qx.split("|");
	  	UserQuery->addItem( decodeBase64(nare.at(0)) , decodeBase64(nare.at(1) ));
	  }
	  if (userquery.size() > 0) {
	  connect(UserQuery, SIGNAL(currentIndexChanged(int)),this, SLOT(user_play_query(int) ));
	  }
}

void PlayQuery::user_play_query( int i )
{
	const QString qu = UserQuery->itemData(i).toString();
	textquery->setPlainText(qu);
	exec_query_user(qu);
	
}


void PlayQuery::show_paste_query( int i )
{
  const QString qu = cachequery->itemData(i).toString();
	textquery->setPlainText(qu);
	exec_query_user(qu);
}
void PlayQuery::show_table( const QString t )
{

          if (t.size() < 1) {

           return;

          }
	  QStringList fe = line->FieldList(t);
		const QString qu = QString("SELECT %2 FROM %1").arg(t).arg(fe.join(","));
	  textquery->setPlainText(qu);
	  exec_query_user(qu);
}


void PlayQuery::exec_query_user( const QString txtq )
{
	QString queryuser;
	QStringList hottables = line->Hotlist();
	bool tload = false;
	if (txtq.size() > 0) {
		queryuser = txtq;
	} else {
		queryuser = textquery->toPlainText().trimmed();
	}
	
	if (queryuser.startsWith("DROP",Qt::CaseInsensitive)) {
		for (int i = 0; i < hottables.size(); ++i)  {
			if (queryuser.contains(hottables.at(i))) {
				QMessageBox::warning(0, tr("Error SQL!"),tr("HotTable not allow drop!"));
				return;
			}
		}
		int rdrop = QMessageBox::question(this, tr("Please confirm to remove table!"),queryuser,
                                                            tr("&Yes"), tr("&No"),
                                                             QString(),8888,9999);
		if (rdrop !=0) {
		return;
		}
		tload = true;
	}
	
	if (queryuser.startsWith("CREATE",Qt::CaseInsensitive)) {
		int rmake = QMessageBox::question(this, tr("Please confirm to create table!"),queryuser,
                                                            tr("&Yes"), tr("&No"),
                                                             QString(),8888,9999);
		if (rmake !=0) {
		return;
		}
		tload = true;
		
	}
	
	  
	
	QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));
	
	model_show = new QSqlQueryModel();
  model_show->setQuery(queryuser,db);
	if (model_show->lastError().isValid()) {
		QApplication::restoreOverrideCursor();
		 QMessageBox::warning(0, tr("Error SQL user query!"),tr("Error SQL message: %1").arg(model_show->lastError().text()));
	return;
	} else {
		tshow->setModel(model_show);
		tshow->resizeRowsToContents();
		tshow->resizeColumnsToContents();
		tshow->setAlternatingRowColors(true);
		QTimer::singleShot(100, this, SLOT(afterwrite())); 
            if (tload) {
            ReloadTables();		
						}
		QApplication::restoreOverrideCursor();
	}
}

void PlayQuery::OpenFileQuery()
{
	QStringList enc;
	foreach (QString s, QTextCodec::availableCodecs())
		enc << s;
	enc.sort();
	
	QString cfileName = QFileDialog::getOpenFileName(0, "Import Mysql query dump", "*.sql");
	if (cfileName.size() < 1) {
	return;
	}
	lineEdit->setText(cfileName);
	QTextCodec *ucodec;
	ucodec = QTextCodec::codecForLocale(); 
	int mibpc = ucodec->mibEnum();
	if (mibpc !=106) {
       const  QString uname = QInputDialog::getItem (0,tr("Select codec from file...."),tr("Codec from file."),enc,enc.indexOf("UTF-8"),false);
	  

		          if (uname.size() > 0) {
		          ucodec = QTextCodec::codecForName(uname.toAscii());
				} else {
				return;
				}
	}
	QStringList pac;
	pac.clear();
   QString multiline;
  QFile file(cfileName); 
    if (file.exists()) {
                if (file.open(QFile::ReadOnly | QFile::Text)) {				
                     QTextStream in(&file);
                                           in.setCodec(ucodec);
									
							while (!in.atEnd()) {
							 QString line = in.readLine();
							bool c = false;
                                                        if ( line.startsWith("DROP") && line.endsWith(";") )  {
                                                         c = true;
							}
                                                        if ( line.startsWith("CREATE") && line.endsWith(";") )  {
                                                         c = true;
							}
                                                         if ( line.startsWith("CREATE") && !line.endsWith(";") &&  line.endsWith("(")) {
                                                         c = false;
                                                         multiline = line;
							}
                                                         if (!line.startsWith("-") && !line.startsWith("#") && multiline.size() > 0  && !line.endsWith(";")  && !line.endsWith("(")) {
                                                         multiline.append(line);
                                                         }
                                                          if (!line.startsWith("-") &&  !line.startsWith("#") && multiline.size() > 0 && line.endsWith(";") && !line.endsWith("(")) {
                                                         multiline.append(line);
                                                          pac.append(multiline);
                                                          multiline = "";
                                                         }

							if (!line.startsWith("-") &&  !line.startsWith("#") && line.startsWith("REPLACE") && line.endsWith(";")) {
							c = true;
							}
							 if (!line.startsWith("-") &&  !line.startsWith("#") && line.startsWith("INSERT") && line.endsWith(";")) {
							 c = true;
							 }
													 	      
													 	
							if (c)  {
							pac.append(line);
							}
													
													
													
													
													 
							} 
									
				 file.close();
				 QStringListModel *InsertQuery = new QStringListModel(pac,listView); 
                                 listView->setModel(InsertQuery); 
                                 listView->setSelectionMode(QAbstractItemView::SingleSelection);
                                          
                                listView->setSelectionRectVisible ( true ) ;
                }
    }
	
	
}


void PlayQuery::ExecQueryList()
{
    const QAbstractItemModel *moqe =  listView->model();
    RowActive = moqe->rowCount();
    QSqlQuery query("",db);



            QProgressDialog *dlg = new QProgressDialog(0,Qt::Popup);
            dlg->setLabelText (tr("Import query on host \"%1\"").arg(db.hostName()));
            dlg->setCancelButton(0);

             dlg->setMaximum(RowActive);

            for (int x = 0; x < RowActive; ++x)  {
                              const QModelIndex index = moqe->index(x,0);
                               listView->scrollTo( index , QAbstractItemView::PositionAtBottom );
                                const QString pari = index.data().toString() ;
                                  textEdit_2->setPlainText(pari);

                                           dlg->setValue(x);
                                 if (pari.size() > 0) {
						if ( !query.exec( pari) ) {

                                                     dlg->close();

                                                       delete dlg;
						 QMessageBox::warning(0, tr("Error SQL File loop!"),tr("Unable to exec query %1").arg(query.lastError().text()));
						return;
						}
                                }
                   }



          dlg->close();

          delete dlg;
     
                                 QStringListModel *InsertQuery = new QStringListModel(QStringList(),listView); 
                                 listView->setModel(InsertQuery); 
                                 lineEdit->setText("");
                                 textEdit_2->setPlainText("");
                                 ReloadTables();
}





void PlayQuery::afterwrite()
{
	tshow->resizeRowsToContents();
	tshow->resizeColumnsToContents();
	tshow->setAlternatingRowColors(true);
	
}

void PlayQuery::export_show()
{
	if (textquery->toPlainText().trimmed().size() > 0 && model_show->rowCount() > 0) {
		
		ModelConverter makece;
		               makece.set(model_show);
		
         DataExportDialog *xexport =  new DataExportDialog(this,model_show,true);
         xexport->SetTable(showtable->currentText());
         xexport->SetDoc(makece.TabletoDom());
         int cango = xexport->exec();
         if (cango == 1) {
             xexport->doExport();
         }
				 
				 
	}
				 
}

void PlayQuery::print_show()
{
	if (textquery->toPlainText().trimmed().size() > 0 && model_show->rowCount() > 0) {
		               ModelConverter makece;
		               makece.set(model_show);
		TextPrinter *textprinter_ = new TextPrinter(this);
    textprinter_->setHeaderSize(12);
    textprinter_->setHeaderText(tr("Mysql table / Print on %1").arg(QDateTime::currentDateTime().toString()));
    textprinter_->preview(makece.TabletoDom(), tr("Print Preview"));
	}
	
}






